USE [DSRSystem]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIndicatorTemplates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIndicatorTemplates]
GO

CREATE PROCEDURE [dbo].[spGetIndicatorTemplates]
AS
BEGIN
	DECLARE @IndicatorTemplates TABLE
	(
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[IndicatorName] [nvarchar](max) NOT NULL,
		[IndicatorNumber] [nvarchar](max) NOT NULL,
		[FormatedIndicatorNumber] [nvarchar](max) NOT NULL,
		[IndicatorSortKey] [int] NOT NULL,
		[ThemeId] [uniqueidentifier] NOT NULL,
		[ThemeName] [nvarchar](max) NOT NULL,
		[ThemeNumber] [int] NOT NULL,
		[ThemeSortKey] [int] NOT NULL,
		[IndicatorCategory] [nvarchar](max) NOT NULL,
		[IndicatorCategorySortKey] [int] NOT NULL
	)
	INSERT INTO @IndicatorTemplates

	SELECT	dbo.Indicators.IndicatorId, 
			dbo.Indicators.Name AS IndicatorName, 
			dbo.Indicators.Number AS IndicatorNumber,
			[dbo].[fFormatIndicatorNumber](dbo.Indicators.Number) AS FormatedIndicatorNumber,
			0 AS IndicatorSortKey, 
			dbo.IndicatorTopics.ThemeId, 
			dbo.IndicatorThemes.Name AS ThemeName, 
			dbo.IndicatorThemes.Number AS ThemeNumber,
            CASE WHEN dbo.IndicatorThemes.Name = 'Environment' THEN 1 ELSE CASE WHEN dbo.IndicatorThemes.Name = 'Social' THEN 2 ELSE CASE WHEN dbo.IndicatorThemes.Name = 'Governance' THEN 3 ELSE CASE WHEN dbo.IndicatorThemes.Name = 'Products' THEN 4 ELSE 0 END END END END AS ThemeSortKey, 
			dbo.IndicatorCategories.Name AS IndicatorCategory, 
			dbo.IndicatorCategories.[Order] AS IndicatorCategorySortKey
	FROM	dbo.Indicators
    INNER JOIN dbo.IndicatorTopics ON dbo.Indicators.TopicId = dbo.IndicatorTopics.TopicId 
    INNER JOIN dbo.IndicatorThemes ON dbo.IndicatorTopics.ThemeId = dbo.IndicatorThemes.ThemeId 
    INNER JOIN dbo.IndicatorCategories ON dbo.Indicators.CategoryId = dbo.IndicatorCategories.IndicatorCategoryId
	WHERE dbo.Indicators.Template = 1 AND dbo.Indicators.IndicatorTypeId = 1

	UPDATE @IndicatorTemplates
	SET IndicatorSortKey = Indicators.IndicatorSortKey 
	FROM
	(
		SELECT IndicatorId AS Id, CONVERT(int, CONVERT(VARCHAR, ThemeSortKey) + 
		RIGHT(CONVERT(VARCHAR, 1000 + ROW_NUMBER() OVER(PARTITION BY ThemeSortKey ORDER BY FormatedIndicatorNumber)), 3)) AS IndicatorSortKey 
		FROM @IndicatorTemplates
	) Indicators
	WHERE Indicators.Id = IndicatorId

	SELECT [IndicatorId]
		,[IndicatorName]
		,[IndicatorNumber]
		,[IndicatorSortKey]
		,[ThemeId]
		,[ThemeName]
		,[ThemeSortKey]
		,[IndicatorCategory]
		,[IndicatorCategorySortKey]
	FROM @IndicatorTemplates
	ORDER BY IndicatorSortKey
END
GO


